using System;
using System.Collections;
using System.Collections.Generic;
using System.Collections.Specialized;
using System.Data;
using System.Data.SqlClient;
using System.DirectoryServices;
using System.Drawing;
using System.IO;
using System.Linq;
using System.Runtime.CompilerServices;
using System.Runtime.InteropServices;
using System.Text.RegularExpressions;
using System.Web;
using Data;
using Data.DataSources;
using Data.LINQ;
using Dimok;
using Dimok.Aspx.Manager.Lib;
using Dimok.Credit;
using Dimok.Credit.DSs;
using Dimok.Data;
using Dimok.Exceptions;
using Dimok.User;
using AccurintRemote = Accurint;
using Config = Dimok.Config;
using M = System.Web.Mail;

namespace Dimok.Aspx.Manager{
	public class UOX:UserObjectEx{

		public void FaxCallBack(){
			if( LoanInfo.DealerFax == "" )
				throw new Exception("Dealer doesn't have a Fax Number!");
			if( LoanInfo.DealerID <= 0 )
				throw new Exception("Dealer is not selected!");
			string src = "http://"+Config.application.LocalWebDomain+"/Manager/Underwriting/Callback.aspx?ApplID="+applID+"&NoLock=1&IsFax=1&EMail="+Mail;
			Dimok.Fax.Frame(src,LoanInfo.DealerFax,UserName,LoanInfo.DealerName);
		}

		#region Applicant Staff
		public override int hasCoApplID{get{return ApplInfo.BorrowerID(1);}}
		public void RemoveCoApplicant(){
			SQL.ExecuteNonQueryEx(db,"DELETE FROM t_Loan_Borrower WHERE LoanID=@ApplID AND Pos = 1",applID);
		}
		public string Dups(){return Dups(applID);}
		public string Dups(int applID){
			try{
				return SQL.ADOToString(dbADO,"SELECT * FROM dbo.Dups("+applID+",DEFAULT)");
			}catch(Exception exc){ throw new ASPException(exc); }
		}
		public string CoApplicantFilter{get{return "ApplicantID = "+hasCoApplID;}}
		
		public string SSN{
			get { return (string)SQL.ExecuteScalarEx(db, "SELECT ISNULL(SSN,'') FROM v_Borrower_0 WHERE " + ApplicantFilter()); }
		}
		#endregion


		#region Applicant Info

		protected override object _applInfo_v{get{return _applInfo==null ? _applInfo=new ApplInfoClass(this) : _applInfo;}}
		public new ApplInfoClass ApplInfo{get{return (ApplInfoClass)_applInfo_v;}}
		public new class ApplInfoClass : UserObjectEx.ApplInfoClass{
			protected override string ApplInfo_V{get{return "SELECT * FROM vx_ApplInfo WHERE "+uox.ApplicantFilter();}}
			public ApplInfoClass(UOX uox):base(uox){}
			public string DriverLicense(){return Get()+"";}

			#region Borrower
			private string Borrower_F(string posParam){return "SELECT * FROM Borrower("+uox.applID+","+posParam+")";}
			public DataSet Borrowers(){return SQL.ExecuteDatasetEx(uox.db,Borrower_F("DEFAULT"));}
			public int BorrowersCount(){
				return (int)SQL.ExecuteScalarEx(uox.db,Borrower_F("DEFAULT").Replace("*","COUNT(*)"));
			}
			public DataSet Borrower(int pos){
				return SQL.ExecuteDatasetEx(uox.db,Borrower_F("@Pos"),pos);
			}
			public string BorrowerXml(int pos){return SQL.XmlInnerText(Borrower(pos));}
			#endregion
			
			#region Address
			private string Address_T(int borrID){return "SELECT * FROM t_Address WHERE BorrowerID="+borrID;}
			public DataSet Address(int borrID, byte addressPos){
				return SQL.ExecuteDatasetEx(uox.db,Address_T(borrID)+" AND Pos=@Pos",addressPos);
			}
			public string AddressXml(int borrID, byte addressPos){return SQL.XmlInnerText(Address(borrID,addressPos));}
			public int AddressCount(int borrID){
				return (int)SQL.ExecuteScalarEx(uox.db,Address_T(borrID).Replace("*","COUNT(*)"));
			}
			#endregion

			#region Employment
			private string Employment_T(int borrID){return "SELECT * FROM t_Employment WHERE BorrowerID="+borrID;}
			public DataSet Employment(int borrID, byte employmentPos){
				return SQL.ExecuteDatasetEx(uox.db,Employment_T(borrID)+" AND Pos=@Pos",employmentPos);
			}
			public string EmploymentXml(int borrID, byte employmentPos){return SQL.XmlInnerText(Employment(borrID,employmentPos));}
			public int EmploymentCount(int borrID){
				return (int)SQL.ExecuteScalarEx(uox.db,Employment_T(borrID).Replace("*","COUNT(*)"));
			}
			#endregion

			public string ReferencesXml(int borrowerID){
				return SQL.XmlInnerText(uox.db,"SELECT * FROM t_References WHERE BorrowerID=@BorrID ORDER BY ID",true,borrowerID);
			}
			public string ReferencesXml(byte borrowerPos){
				return SQL.XmlInnerText(uox.db,"SELECT * FROM t_References WHERE Borrower"+uox.BorrowerFilter(borrowerPos)+" ORDER BY ID",true);
			}
			public string InterviewsXml(int borrowerID){
				return SQL.XmlInnerText(uox.db,"SELECT * FROM t_Interviews WHERE BorrowerID=@BorrID ORDER BY ID",true,borrowerID);
			}
			public string InterviewsXml(byte borrowerPos){
				return SQL.XmlInnerText(uox.db,"SELECT * FROM t_Interviews WHERE Borrower"+uox.BorrowerFilter(borrowerPos)+" ORDER BY ID",true);
			}
		}

		#endregion ApplInfo

		#region Accurint
		public class AccurintClass{
			protected UOX uox;
			public AccurintClass(UOX uox){
				this.uox = uox;
			}
			public string[] InstantID(byte borrowerPos){
				AccurintRemote.PersonDS.AccurintDataTable dt = (AccurintRemote.PersonDS.AccurintDataTable)SQL.ExecuteDatasetEx(uox.db,new AccurintRemote.PersonDS().Accurint,"SELECT * FROM v_Accurint WHERE Borrower"+uox.BorrowerFilter(borrowerPos));
				return InstantID(dt[0]);
			}
			public string[] InstantID(int borrowerID){
				AccurintRemote.PersonDS.AccurintDataTable dt = (AccurintRemote.PersonDS.AccurintDataTable)SQL.ExecuteDatasetEx(uox.db,new AccurintRemote.PersonDS().Accurint,"SELECT * FROM v_Accurint WHERE BorrowerID=@BID",borrowerID);
				return InstantID(dt[0]);
			}
			public string[] InstantID(AccurintRemote.PersonDS.AccurintRow dr){
				return InstantID(
					dr.FirstName,dr.LastName,dr.SSN,dr.Street,dr.CityName,dr.StateCode,dr.Zip,dr.DOB,dr.PhoneHome,"");
			}
			public string[] InstantID(string firstName,string lastName,string SSN,string street,string city,string state,string zip,string DOB,string homePhone,string driverLicense){
				SSN = Regex.Replace(SSN,"[^0-9]","").Trim();
				if(!Regex.IsMatch(SSN,"^\\d{9}$")) throw new Exception("Incorrect SSN["+SSN+"]!");
				homePhone = Regex.Replace(homePhone,"[^0-9]","").Trim();
				if(!Regex.IsMatch(homePhone,"^(|\\d{10})$")) throw new Exception("Incorrect home phone ["+homePhone+"]!");
				DOB = DOB.Trim();
				if(!Regex.IsMatch(DOB,@"^(|[\d/]{0,3}[\d/]{0,3}\d{4})$")) throw new Exception("Incorrect DOB [" + DOB + "]!");
				AccurintRemote.PersonDS dsInstID = new AccurintRemote.Client().GoInstantID(
					firstName,lastName,SSN,street,city,state,zip,DOB,homePhone,driverLicense);
				new BorrowersDataSource().UpdateFields(
					new OrderedDictionaryEx("InstantIDScore", dsInstID.InstantID[0].Score, "InstantIDHtml", dsInstID.InstantID[0].Raw),
					new OrderedDictionaryEx("SSN", SSN));
				return new string[]{dsInstID.InstantID[0].Score+"",dsInstID.InstantID[0].Raw};
			}
			public string DriverLicense(){return DriverLicense(uox.ApplInfo.DriverLicense());}
			public static string DriverLicense(string driverLicense){
				if( (driverLicense+"") == "" )return "<H3>Driver License is not in provided.</H3>";
				return new AccurintRemote.Client().GoDriverLicense(driverLicense);
			}
			public static AccurintRemote.PersonDS.InfoDataTable GoPeopleSearch(string SSN) {
				return new AccurintRemote.Client().GoPeopleSearch(SSN).Info;
			}
			public static AccurintRemote.PersonDS.InfoDataTable GoPeopleAtWork(string SSN) {
				return new AccurintRemote.Client().GoPeopleAtWork(SSN).Info;
			}
		}
		public AccurintClass Accurint{get{return _accurint == null ? _accurint=new AccurintClass(this) : _accurint;}}
		private AccurintClass _accurint;
		#endregion

		#region Credit Info

		protected object _Creditnfo{get{ return _creditInfo == null ? _creditInfo = new CreditInfoClass(this) : _creditInfo;}}
		public CreditInfoClass CreditInfo{get{ return (CreditInfoClass)_Creditnfo;}}
		public class CreditInfoClass:InfoBase{
			public CreditInfoClass(UOX uox):base(uox){}
			public override string SQL_V{get{return "SELECT * FROM v_CreditInfo WHERE "+uox.ApplicantFilter();}}
			public override string SQL_U{get{return "SELECT * FROM v_CreditInfo WHERE "+uox.ApplicantFilter();}}
			public override void Update(string fieldName,string fieldValue){
				SQL.ExecuteNonQueryEx(uox.db,"UPDATE v_CreditInfo SET "+SQL.CheckInjects(fieldName)+" = @V WHERE "+uox.ApplicantFilter(),fieldValue);
			}
		}

		#endregion

		#region Tracker
		public class TrackerClass : InfoBase{
			protected int _trackerRequestID;
			public string Warning = "";
			public int TrackerRequestID{get{return _trackerRequestID;}}
			protected override string DS_Error(int rowCount) {
				if( rowCount == 0 )return "Tracker Request was not found.";
				return rowCount+" Tracker Requests was not found.";
			}


			#region Constructors
			public TrackerClass(UOX uox) : base(uox){}
			public TrackerClass(UOX uox,string vin,int dealerID) : base(uox){
				if( vin == "" || dealerID == 0 )throw new Exception("Not enough info to identify a Tracker Request");
				int[] trackerRequestIDs = TrackerRequestIDs(vin,dealerID);
				if( trackerRequestIDs.Length != 1 )throw new Exception("Inproper number ["+trackerRequestIDs.Length+"] of Tracker Requests");
				_trackerRequestID = trackerRequestIDs[0];
			}
			public TrackerClass(UOX uox,int trackerRequestID) : base(uox){
				_trackerRequestID = trackerRequestID;
			}
			public TrackerClass(UOX uox,int trackerRequestID,string warning) : this(uox,trackerRequestID){
				Warning = warning;
			}
			public TrackerClass(UOX uox, bool createNew) : base(uox){
				_trackerRequestID = (int)SQL.ExecSP(db,"s_TrackerRequest_New");
			}
			public static int[] TrackerRequestIDs(string vin){return TrackerRequestIDs(vin,0);	}
			public static int[] TrackerRequestIDs(string vin,int dealerID){
				ArrayListEx alFilter = new ArrayListEx();
				ArrayListEx alParams = new ArrayListEx();
				if( (vin+"") != "" ){alFilter.Add("VIN=@VIN");alParams.Add(vin);}
				if( dealerID > 0 ){alFilter.Add("DealerID=@DID");alParams.Add(dealerID);}
				if( alFilter.Count == 0 )throw new Exception("VIN or DealerID must be provided.");
				return SQL.ExecuteScalarArray(dbRaw,
"SELECT ID FROM t_TrackerRequest WHERE "+alFilter.Join(" AND ")+" ORDER BY StatusDate DESC",alParams.ToArray());
			}
			#endregion

			public int TechnicianID{get{return GetInt();}}
			public string VIN{get{return GetString();}}
			public int DealerID{get{return GetInt();}}
			public int LoanID{get{return GetInt();}}
			public int BranchID{get{return GetInt();}}

			#region Tracker Request
			public DataSet RequestStatusList(){return SQL.ExecuteDatasetEx(db,"SELECT * FROM  RequestStatusList(@TRID)",TrackerRequestID);}
			
			public DataSet RequestHistory(){
				return SQL.ExecuteDatasetEx(db,"SELECT * FROM hist_TrackerRequest(@TRID) ORDER BY HID DESC",TrackerRequestID);
			}
			
			public DataSet RequestNote(string note){
				SQL.ExecuteDatasetEx(db,"INSERT INTO t_TrackerRequestNotes(TrackerRequestID,Note) VALUES(@TRID,@Note)",TrackerRequestID,note);
				return RequestNotes();
			}
			public DataSet RequestNotes(){
				return SQL.ExecuteDatasetEx(db,"SELECT * FROM v_TrackerRequestNotes WHERE TrackerRequestID=@TRID ORDER BY DateStamp DESC",TrackerRequestID);
			}
			public static DataSet RequestNotesList(){
				return SQL.ExecuteDatasetEx(dbRaw,"SELECT DISTINCT Note FROM t_TrackerRequestNotes");
			}

			#endregion

			#region Technician(Tracker/Car)
			public DataSet Technician(){return Technician(TrackerRequestID);}
			public DataSet Technician(int technicianID){return SQL.ExecuteDatasetEx(db,"SELECT * FROM v_Technician WHERE ID=@ID",technicianID);}

			public DataSet Technicians(){return SQL.ExecuteDatasetEx(db,"SELECT * FROM v_Technician ORDER BY OrderBy,Name");}

			public DataSet Technician_Trackers(){return Technician_Trackers(db,TechnicianID);}
			public DataSet Technician_Trackers(int technicianID){return Technician_Trackers(db,technicianID);}
			public static DataSet Technician_Trackers(string conn,int technicianID){return SQL.ExecuteDatasetEx(conn,"SELECT *,CONVERT (varchar, DateStamp, 111) Date FROM v_Technician_Trackers WHERE TechnicianID=@TechID ORDER BY DateStamp",technicianID);}

			public DataSet Technician_Cars(){return Technician_Cars(TechnicianID);}
			public static DataSet Technician_Cars(int technicianID){return SQL.ExecuteDatasetEx(UOX.dbRaw,"SELECT * FROM Technician_Cars(@TechID)",technicianID);}

			public string Technician_Requests(){return Technician_Requests(TechnicianID);}
			public string Technician_Requests(int technicianID){return Technician_Requests(dbADO,technicianID);}
			public static string Technician_Requests(string connAdo,int technicianID){
				string sql = "SELECT Dealer, StatusName, VIN, Description, LoanID, TrackerRequestID FROM v_TrackerRequest WHERE";
				if( technicianID != 0 )sql += " TechnicianID = ? AND";
				else sql = sql.Replace("SELECT ","SELECT  Technician,");
				return SQL.ADOToString(connAdo,sql+" IsActive = 1 ORDER BY Technician, Dealer",technicianID);}
			
			[MethodImplAttribute(MethodImplOptions.NoInlining)]
			public static void Technician_Tracker_InHands(int technicianID,string trackerID){SQL.ExecSP(dbRaw,"s_"+Dimok.Common.GetFuncName(),technicianID,trackerID);}

			public DataSet TechniciansByDistance(){
				return SQL.ExecuteDatasetEx(dbRaw,"s_TechniciansByDistance",DealerID);
			}
			#endregion

			#region Transactions(Pay to Installer)
			public string TransactionsByTechnician(){
				if( TrackerRequestID > 0 )
					return Transactions("TechnicianID="+TechnicianID+" AND StatusID=98");
				return Transactions("1 = 0");
			}
			public string Transactions(string filter){
				if( filter != "" )filter = " WHERE "+SQL.CheckInjects(filter.Replace("*","%"));
				return SQL.ADOToString(uox.dbADO,"SELECT * FROM v_TrackerTransaction"+filter+" ORDER BY Date DESC");
			}
			#endregion

			#region Tracker Licenses
			public string TrackerLicensesAllBranchesXml(){return SQL.XmlInnerText(TrackerLicenses(db,0));}
			public string TrackerLicensesXml(int branchID){return SQL.XmlInnerText(TrackerLicenses(db,branchID));}
			public string TrackerLicensesXml(){return SQL.XmlInnerText(TrackerLicenses(db,TrackerRequestID>0?BranchID:0));}
			private static DataSet TrackerLicenses(string conn){return TrackerLicenses(conn,0);}
			private static DataSet TrackerLicenses(string conn,int branchID){
				return SQL.ExecuteDatasetEx(conn,"SELECT * FROM v_TrackerLicenses WHERE @BranchID IN(ID,0)",branchID);
			}
			#endregion

			#region Tracker
			public static DataRow TrackerInfo(string trackerID){
				return SQL.ExecuteDatasetEx(dbRaw,"SELECT * FROM v_TrackerInfo WHERE TrackerID=@ID",trackerID).Tables[0].Rows[0];
			}
			
			public DataSet Trackers(){
				return SQL.ExecuteDatasetEx(db,"SELECT * FROM v_Tracker_Car WHERE TrackerRequestID=@TRID",TrackerRequestID);
			}
			public DataSet TrackersByVIN(){
				return SQL.ExecuteDatasetEx(dbRaw,
					"SELECT * FROM t_Tracker_Car WHERE VIN=(SELECT VIN FROM t_TrackerRequest WHERE ID=@TRID)",TrackerRequestID);
			}
			public static DataSet Trackers(string vin){
				return SQL.ExecuteDatasetEx(dbRaw,"SELECT * FROM v_Tracker_Car WHERE VIN=@VIN",vin);
			}

			public DataSet TrackersByLoan(){
				return SQL.ExecuteDatasetEx(db,"SELECT * FROM v_Tracker_Car WHERE "+uox.ApplicantFilter());
			}
				[MethodImplAttribute(MethodImplOptions.NoInlining)]
			public static void Tracker_InHouse(string trackerID){SQL.ExecSP(dbRaw,"s_"+Dimok.Common.GetFuncName(),trackerID);}

			public static DataSet TrackerStatusList(string trackerID){
				return SQL.ExecuteDatasetEx(dbRaw,"SELECT MenuID,MenuName,def FROM dbo.TrackerStatusList(@ID) ORDER BY WorkFlowIndex",trackerID);
			}
			public static DataSet Trackers_InHouse(){
				DataTable dt = new DataSet().Tables.Add("Trackers");
				dt.Columns.Add("AdoString");
                dt.Rows.Add(new object[] { SQL.ADOToString(dbRawAdo, "SELECT ID FROM v_Tracker__InHouse") });
				return dt.DataSet;
			}
			#endregion
		
			public static string SQL_T{get{return "SELECT * FROM t_TrackerRequest WHERE ";}}
			public override string SQL_V{get{return "SELECT * FROM v_TrackerRequest WHERE TrackerRequestID="+TrackerRequestID;}}
			public override string SQL_U{get{return SQL_V;}}

			public class VinProblemException:Exception{
				public VinProblemException(string message):base(message){}
			}
		}
		public TrackerClass Tracker(int trackerRequestID){
			return trackerRequestID > 0 ? new TrackerClass(this,trackerRequestID) : new TrackerClass(this);
		}
		public TrackerClass Tracker(){return new TrackerClass(this);}
		public TrackerClass Tracker(bool createNew){return new TrackerClass(this,createNew);}
		public TrackerClass Tracker(string vin,bool createNew){return Tracker(vin,0,createNew);}
		public TrackerClass Tracker(string vin,int dealerID,bool createNew){
			if( createNew ){
				try{
					return new TrackerClass(this,(int)SQL.ExecSP(this.db,"s_TrackerRequest_New",vin));
				}catch(Dimok.Data.Database.ExecSpException exc){
					if( exc.ReturnValue > 0 )return new TrackerClass(this,exc.ReturnValue,ClientException.GetMessages(exc));
					throw;
				}
			}
			if( vin == "" || dealerID <= 0 )return new TrackerClass(this,0);
			int[] IDs = TrackerClass.TrackerRequestIDs(vin,dealerID);
			if( IDs.Length == 0 )
				throw new TrackerClass.VinProblemException("No Tracker Requests exist with VIN ["+vin+"].");
			if( IDs.Length > 100 )
				throw new TrackerClass.VinProblemException("Multiple ["+IDs.Length+"] Tracker Requests exist.");
			return Tracker(IDs[0]);
		}
		#endregion Tracker

		#region NeedApproval
		public bool NeedApproval(){return NeedApproval(ApplID0);}
		public bool NeedApproval(int applID){
			return (bool)SQL.ExecuteScalarEx(db,"SELECT ISNULL(dbo.MenuStatus_NeedApproval(@ApplID),0)",applID);
		}
		#endregion NeedApproval

    #region EmploymentVer ==================
		public string JobNum{get{return GetQueryParam("JobNum");}}
		public int applEmpl{
			get{
				string coAppl = GetQueryParam("CoAppl");
				if( coAppl != "" && hasCoApplID == 0 )throw new ClientException("No Co-Applicant.");
				return coAppl==""?applIDSec:hasCoApplID;
			}
		}
		public string I_EmploymentVer{
			get{
				return "INSERT INTO t_EmploymentVer(ApplicantID,[Index]) VALUES("+applEmpl+","+(JobNum==""?0:1)+")";
			}
		}
		public string U_EmploymentVer(){
			return "SELECT * FROM t_EmploymentVer WHERE ApplicantID="+applEmpl+" AND [Index]="+(JobNum==""?0:1);
			//return "UPDATE t_EmploymentVer SET "+fieldName+"=@Field WHERE ApplicantID="+applEmpl+" AND [Index]="+(JobNum==""?0:1);
		}
		public string v_EmploymentVer{
			get{
				return "EXEC s_EmploymentVer_Add "+applEmpl+","+(JobNum==""?0:1)+"; SELECT * FROM v_EmploymentVer"+JobNum+" WHERE ApplicantID="+applEmpl;
			}
		}
		public DataSet EmploymentVer{get{return SQL.ExecuteDataset(db,CommandType.Text,v_EmploymentVer);}}
		#endregion EmploymentVer ==================

		#region Verification

		protected override object _verification_v { get { return _verification == null ? _verification = new VerificationClass(this) : _verification; } }
		public new VerificationClass Verification { get { return (VerificationClass)_verification_v; } }
		public new class VerificationClass : UserObjectEx.VerificationClass {
			protected override IDictionary Keys { get { return new OrderedDictionaryEx("LoanID", ApplID); } }
			protected override Data.DataSources.AObjectDataSourceBase DSObject {
				get { return _dsObject != null ? _dsObject : _dsObject = new VerificationSmartDataSource(); }
			}
			public VerificationClass(UOX uox) : base(uox) { }
			public override int StatusID { get { return GetInt(); } 
}
			public DataTable SetStatus(object stateID) {
				DataTable dt = DSObject.UpdateField(Keys, "StatusID", stateID,true);
				return NextStatuses((int)dt.Rows[0]["StatusID"]);
			}
			public override DataTable NextStatuses(object statusID) {
				return new StateFlowNextStatesDataSource().SelectEx((int)statusID).Table;
			}
		}
		#endregion

		public abstract class InfoBaseDataSourceLoan : InfoBaseDataSource {
			protected override IDictionary Keys { get { return new OrderedDictionaryEx("LoanID", ApplID); } }
			public InfoBaseDataSourceLoan(int applID) : base(applID) { }
			public InfoBaseDataSourceLoan(UOX uox) : base(uox) { }
			public int StatusID { get { return GetInt(); } }
			public virtual DataTable SetStatus(object stateID) {
				DataTable dt = DSObject.UpdateFields(new OrderedDictionaryEx("StatusID", stateID), Keys, ";");
				return NextStatuses((int)dt.Rows[0]["StatusID"]);
			}
			public DataTable NextStatuses() { return NextStatuses(StatusID); }
			public DataTable NextStatuses(object statusID) {
				return new StateFlowNextStatesDataSource().SelectEx((int)statusID).Table;
			}
		}

		#region Collection
		protected CollectionClass _Collection = null;
		public CollectionClass Collection { get { return _Collection == null ? _Collection = new CollectionClass(this) : _Collection; } }
		public class CollectionClass : InfoBaseDataSourceLoan {
			protected override Data.DataSources.AObjectDataSourceBase DSObject {
				get { return _dsObject != null ? _dsObject : _dsObject = new CollectionsDataSource(); }
			}
			public CollectionClass(UOX uox) : base(uox) { }
			public override DataTable SetStatus(object stateID) {
				DataTable dt = DSObject.UpdateFields(new OrderedDictionaryEx("StatusID", stateID,"LoanID",ApplID), Keys, ";");
				return NextStatuses((int)dt.Rows[0]["StatusID"]);
			}
		}
		#endregion

		#region BuyBack
		protected BuyBackClass _BuyBack = null;
		public BuyBackClass BuyBack { get { return _BuyBack == null ? _BuyBack = new BuyBackClass(this) : _BuyBack; } }
		public class BuyBackClass : InfoBaseDataSourceLoan {
			protected override Data.DataSources.AObjectDataSourceBase DSObject {
				get { return _dsObject != null ? _dsObject : _dsObject = new BuyBacksDataSource(); }
			}
			public BuyBackClass(int applID) : base(applID) { }
			public BuyBackClass(UOX uox) : base(uox) { }
			public override DataTable SetStatus(object stateID) {
				DataTable dt = DSObject.UpdateFields(new OrderedDictionaryEx("StatusID", stateID, "LoanID", ApplID), Keys, ";");
				return NextStatuses((int)dt.Rows[0]["StatusID"]);
			}
		}
		#endregion

		#region Repossession
		protected RepoClass _Repo = null;
		public RepoClass Repo { get { return _Repo == null ? _Repo = new RepoClass(this) : _Repo; } }
		public class RepoClass : InfoBaseDataSourceLoan {
			protected override Data.DataSources.AObjectDataSourceBase DSObject {
				get { return _dsObject != null ? _dsObject : _dsObject = new ReposDataSource(); }
			}
			public RepoClass(int applID) : base(applID) { }
			public RepoClass(UOX uox) : base(uox) { }
			public override DataTable SetStatus(object stateID) {
				DataTable dt = DSObject.UpdateFields(new OrderedDictionaryEx("StatusID", stateID, "LoanID", ApplID), Keys, ";");
				return NextStatuses((int)dt.Rows[0]["StatusID"]);
			}
		}
		#endregion

		#region Bankruptcy
		protected BankruptcyClass _Bankruptcy = null;
		public BankruptcyClass Bankruptcy { get { return _Bankruptcy == null ? _Bankruptcy = new BankruptcyClass(this) : _Bankruptcy; } }
		public class BankruptcyClass : InfoBaseDataSourceLoan {
			protected override Data.DataSources.AObjectDataSourceBase DSObject {
				get { return _dsObject != null ? _dsObject : _dsObject = new BankruptciesDataSource(); }
			}
			public BankruptcyClass(int applID) : base(applID) { }
			public BankruptcyClass(UOX uox) : base(uox) { }
			public override DataTable SetStatus(object stateID) {
				DataTable dt = DSObject.UpdateFields(new OrderedDictionaryEx("StatusID", stateID, "LoanID", ApplID), Keys, ";");
				return NextStatuses((int)dt.Rows[0]["StatusID"]);
			}
		}
		#endregion



		#region LoanInfo
		protected override object _LoanInfo{get{ return _loanInfo == null ? _loanInfo = new LoanInfoClass(this) : _loanInfo;}}
		public new LoanInfoClass LoanInfo{get{ return (LoanInfoClass)_LoanInfo;}}
		public new class LoanInfoClass : UserObjectEx.LoanInfoClass{
			public IDictionary Keys { get { return new OrderedDictionaryEx("LoanID", uox.applID); } }
			public Dimok.Data.Database.KeysFilter KeyFilter{get{return new Dimok.Data.Database.KeysFilter(Keys);   }}
			new UOX uox { get { return (UOX)base.uox; } }
			public LoanInfoClass(UOX uox):base(uox){}

			#region DS
			public override DataSet DS() { return DS(false); }
			public DataSet DS(bool refresh) {
				if (_ds == null || refresh) {
					//_ds = new LoanInfosDataSource().SelectEx(uox.applID).Table.DataSet;
					_ds = new MotoBank().v_TruthInLendings.GetDataSet(til => til.LoanID == uox.applID,"LoanInfo");
					DataRow dr = Row;
				}
				return _ds;
			}

			#endregion

			public string BuyersOrderXml() { return BuyersOrder().GetXml(); }
			public DataSet BuyersOrder() {
				return SQL.ExecuteDatasetEx(uox.db, "SELECT * FROM v_BuyersOrder WHERE " + KeyFilter.Filter, KeyFilter.Params);
			}
			public new IQueryable<v_BuyersOrder_Update> Update(string fieldName, string fieldValue) { return Update(fieldName, fieldValue, false); }
			public IQueryable<v_BuyersOrder_Update> Update(string fieldName, string fieldValue, bool returnResult) {
				return new MotoBank().v_BuyersOrder_Updates.Update(s => s.LoanID == uox.applID, new Dictionary<string, object>() { { fieldName, fieldValue } });
				//return new LoanInfosDataSource().UpdateField(Keys, fieldName, fieldValue,returnResult);
			}
			public DataSet PaymentsSchedule(){return SQL.ExecuteDatasetEx(uox.db,"SELECT * FROM PaymentScheduleByLoanID(@LoanID)",uox.applID);}
			public int TechnicianID { get { return (int)Get(); } }
			public string VIN { get { return Get() + ""; } }
			DataTable _nestStatuses;
			public int StatusID {
				get { return (int)Get(); }
			}
			public DataView SetStatus(object stateID){
				DataTable dt = SQL.UpdateFields(uox.db, "t_Loan","", new OrderedDictionaryEx("ID",uox.applID), "StatusID", stateID);
				return NextStatuses((int)dt.Rows[0]["StatusID"]);
			}
			public DataView NextStatuses() { return NextStatuses(StatusID); }
			public DataView NextStatuses(object statusID) { 
				DataView dv = new StateFlowNextStatesDataSource().SelectEx((int)statusID);
				dv.Table.TableName = "NextStatuses";
				return dv;
			}

			#region Borrower
			private string Borrower_F(string posParam) { return "SELECT * FROM Borrower(" + uox.applID + "," + posParam + ")"; }
			public DataSet Borrowers() { return SQL.ExecuteDatasetEx(uox.db, Borrower_F("DEFAULT")); }
			public byte BorrowersCount() {
				return (byte)SQL.ExecuteScalarEx(uox.db, Borrower_F("DEFAULT").Replace("*", "CONVERT(tinyint,COUNT(*))"));
			}
			public DataSet Borrower(int pos) {
				return SQL.ExecuteDatasetEx(uox.db, Borrower_F("@Pos"), pos);
			}
			public string BorrowerXml(int pos) { return SQL.XmlInnerText(Borrower(pos)); }
			#endregion
		}
		#endregion

		#region Dealer Info
		protected DataSet dealerInfo = null;
		public DataSet DealerInfo{
			get{
				if( dealerInfo != null )return dealerInfo;
				return dealerInfo = SQL.ExecuteDatasetEx(db,"SELECT * FROM v_LoanInfo WHERE "+ApplicantFilter());
			}
		}

		public DataSet DealerSalesPersons(){return DealerSalesPersons(LoanInfo.DealerID);}
		public DataSet DealerSalesPersons(string dealerID){return DealerSalesPersons(int.Parse(dealerID));}
		public DataSet DealerSalesPersons(int dealerID){
			return SQL.ExecuteDatasetEx(db,"SELECT ID,Name FROM t_Dealer_SalesPersons WHERE DealerID=@ID",dealerID);
		}

		public int dealershipID {
			get{
				try{
					object did = Get("description");
					return did == null || did.ToString() == "" ? -1 : int.Parse(did.ToString());
				}catch(Exception){return -1;}
			}
		}
		#endregion
		
		#region CallBack =====================
		public DataSet CallBack(string histID){
			if( histID == null || histID == "" )return CallBack();
			if( histID.ToLower() == "last" )return CallBackLast();
			return SQL.ExecuteDatasetEx(db,"SELECT * FROM dbo.fs_FaxHistory(@ApplID) WHERE HID=@HistID",applID,histID);
		}
		public DataSet CallBack(){
			string sql = "SELECT * FROM v_FaxToDealer WHERE "+ApplicantFilter();
			DataSet ds = SQL.ExecuteDatasetEx(db,sql);
			if( ds.Tables[0].Rows.Count == 0 )throw new Exception("No callback info.");
			return ds;
		}
		public DataSet CallBackLast(){
			string sql = @"SELECT vh.* FROM vhist_v_FaxToDealer vh INNER JOIN vhist_v_FaxToDealer_Last vhl ON vh.HID = vhl.HID WHERE (vhl.PK_1 = @ApplID)";
			DataSet ds = SQL.ExecuteDatasetEx(db,sql,applIDSec);
			return ds;
		}
		public string CallBackXML{get{return SQL.XmlInnerText(CallBack());}}
		public void CallBack_Fax(){
			M.MailMessage m = new System.Web.Mail.MailMessage();
			m.To = Config.application.FaxEMail;
			m.From = UserName;
			m.Subject = LoanInfo.DealerName+"[fax:"+LoanInfo.DealerFax+"]";
			m.Body = "<URLS><URL>http://air_:8080/Manager/Underwriting/Callback.aspx?ApplID="+applID+"&NoLock=1</URL></URLS>";
			M.SmtpMail.SmtpServer = Config.application.MailServer;
			M.SmtpMail.Send(m);
		}
		public DirectoryEntry[] CallBackBuyers{
			get{return IsInDomain ? ADs.LDAP.GetGroupUsers(GroupDN("Underwriting")) : new DirectoryEntry[]{};}
		}
		public DataSet CallBackConditions{get{return SQL.ExecuteDatasetEx(db,"s_CallBackConditions",applID);}}
		public DataSet CallBackConditionList{get{return SQL.ExecuteDatasetEx(db,"s_CallBackConditions",applID,true);}}

		#endregion CallBack =====================

		//*
		#region Helpers ============
		public static string MapSafe(string path,HttpServerUtility server){
			return path.IndexOf(":") < 0 ? server.MapPath(path) : path;
		}
		public static bool FileExists(string filePath,HttpServerUtility server){return File.Exists(MapSafe(filePath,server));}
		public static string ReadFile(string path,string fileName,HttpServerUtility server){return ReadFile(Path.Combine(path,fileName),server);}
		public static string ReadFile(string path,HttpServerUtility server){
			path = MapSafe(path,server);
			string text = "";
			if( !File.Exists(path) )return "";
			StreamReader sr = new StreamReader(path);
			text = sr.ReadToEnd();
			sr.Close();
			return text;
		}
		public static byte[] ReadBytes(string path,string fileName,HttpServerUtility server){return ReadBytes(Path.Combine(path,fileName),server);}
		public static byte[] ReadBytes(string path, HttpServerUtility server) {
			path = MapSafe(path,server);
			byte[] nol = new byte[1];
			if( !File.Exists(path) )return nol;
			FileStream fs = new FileStream(path, FileMode.Open, FileAccess.Read);
			byte[] ba = new byte[fs.Length];
			fs.Read(ba, 0, (int)fs.Length);
			fs.Close();
			return ba;
		}
		#endregion Helpers
		
		#region Verification ==============
		public DataSet VerificationSmart{
			get{
				return SQL.ExecuteDatasetEx(db,CommandType.Text,"EXEC smenu_Verification @ApplID;SELECT * FROM v_VerificationSmart_X WHERE "+ApplicantFilter(),applID);
			}
		}
		public override string Verifyer{
			get{
				DataRowCollection rows = VerificationSmart.Tables[0].Rows;
				return rows[0]["Verifier"].ToString();
			}
		}
		#endregion Verification ==============

		#region Credit Reportd ========
		/*
		public string CreditReport{get{return GetCreditReport(applID);}}
		public string[] CreditReports{get{return GetCreditReports(applID);}}

		public string GetCreditReport(int applID){
			string[] reports = GetCreditReports(applID);
			return string.Join("\n",reports);
		}
		public string[] GetCreditReports(int applID){
			string[] reports = new string[]{"",""};
			DataSet ds = SQL.ExecuteDataset(db,"sec_CreditReportText",applID);
			DataRowCollection rows = ds.Tables[0].Rows;
			for(int i=0; i < rows.Count; i++)
				reports[i] = rows[i]["HumanReadable"].ToString();
			if( rows.Count == 1 )return new string[]{reports[0]};
			if( reports[1] == "" ){
				Match m = Regex.Match(reports[0],@"^(?<r1>[\s\S]+)"+Dimok.Config.credit.equifax.JointToken+@"(?<r2>[\s\S]+)$",RegexOptions.IgnoreCase);
				if( m.Success ){
					reports[0] = m.Groups["r1"].Value;
					reports[1] = m.Groups["r2"].Value;
				}
			}
			return reports;
		}
		public void ParseCredit(CreditBureau bureau){ParseCredit(applID,bureau);}
		public void ParseCredit(int applID,CreditBureau bureau){ParseCredit(applID,null,bureau);}
		public void ParseCredit(int applID,string[] reports,CreditBureau bureau){
			if( reports == null )reports = GetCreditReports(applID);
			Trades dsTrades = null;
			switch( bureau ){
				case CreditBureau.Equifax:
					dsTrades =  (new Equifax()).GetCreditInfo(reports);
					break;
				case CreditBureau.Experian:
					throw new Exception("Experian is not implemented!");
				case CreditBureau.TransUnion:
					throw new Exception("TransUnion is not implemented!");
			}
			SaveCredit(dsTrades,applID,GetCoApplID(applID,false));
		}
		public void PullCredit(CreditBureau bureau){
			PullCredit(applID,bureau);
		}
		public void PullCredit(int applID,CreditBureau bureau){
			PullCredit( applIDSec,"SELECT * FROM vx_ApplicantForCredit WHERE ",bureau);
		}
		#region CreditRequest Structure
		private class CreditRequest{
			public object[] SSN = new object[]{"",""};
			public object[] FirstName = new object[]{"",""};
			public object[] LastName = new object[]{"",""};
			public object[] City = new object[]{"Maimi",""};
			public object[] State = new object[]{"FL",""};
			public void Init(int index,object ssn,object firstName,object lastName,object city,object state){
				SSN[index] = ssn;
				FirstName[index] = firstName == DBNull.Value || firstName.ToString() == "" ? "XXX" : firstName;
				LastName[index] = lastName;
				if( city != DBNull.Value && city.ToString() != "" && state != DBNull.Value && state.ToString() != "" ){
					State[index] =  state;
					City[index] = city;
				}
			}
		}
		#endregion CreditRequest Structure
		/// <summary>
		/// Pull credit and save it Database
		/// </summary>
		/// <param name="applID"></param>
		/// <param name="sql">Applicant info SQL without ApplicantFilter</param>
		/// <param name="bureau"></param>
		public void PullCredit(int applID,string sql,CreditBureau bureau){
			DataSet dsAFC = SQL.ExecuteDatasetEx(db,sql+ApplicantFilter(applID));
			DataRow row = dsAFC.Tables[0].Rows[0];
			bool pulled = row["PulledEx"] != System.DBNull.Value && (bool)row["PulledEx"] && (bool)row["Pulled"];
			int coAppl = row["CoApplicantID"] != System.DBNull.Value ? (int)row["CoApplicantID"] : 0;
			bool pulled1 = coAppl == 0 || row["PulledEx1"] != System.DBNull.Value && (bool)row["PulledEx1"] && (bool)row["Pulled1"];
			string[] reports = GetCreditReports(applID);
			if( !pulled || !pulled1 ){//throw new CreditAlreadyExistsException(applID);
				CreditRequest crReq = new CreditRequest();
				Equifax efx = new Equifax();
				if( !pulled ){
					crReq.Init(0,row["SSN"],row["FirstName"],row["LastName"],row["City"],row["State"]);
					reports[0] = efx.PullCredit(crReq.SSN[0],crReq.FirstName[0],crReq.LastName[0],crReq.City[0],crReq.State[0])[0];
				}
				if( !pulled1 ){
					crReq.Init(0,row["SSN1"],row["FirstName1"],row["LastName1"],row["City"],row["State"]);
					reports[1] = efx.PullCredit(crReq.SSN[0],crReq.FirstName[0],crReq.LastName[0],crReq.City[0],crReq.State[0])[0];
				}
			}
			ParseCredit(applID,reports,bureau);
		}
		public bool CheckReportError(string textReport){
			try{
				Dimok.Credit.Equifax.CheckReportError(textReport);
			}catch(CreditReportException){return true;}
			return false;
		}
		private void SaveCredit(Trades dsTrades,int applID,int coApplID){
			applID = GetSecuredApplID(applID);
			//SQLHelperEx.ExecuteNonQueryEx(db,"DELETE FROM t_TradeReferences WHERE ApplicantID=@ApplID",applID);
			string where = " WHERE ApplicantID="+applID;
			using(SqlConnection conn = new SqlConnection(db)){
				if( dsTrades.t_Applicants.Rows.Count > 0 ){
					dsTrades.t_Applicants.PrimaryKey = new DataColumn[]{dsTrades.t_Applicants.Columns["ApplicantID"]};
					string sql = "SELECT "+SQL.GetColumnNamesString(dsTrades.t_Applicants)+" FROM t_Applicants ";
					if( dsTrades.t_Applicants.Rows.Count > 1 )
						where += " OR ApplicantID = " + coApplID;// SQLHelperEx.ExecuteScalar(db,"SELECT ApplicantID FROM t_Applicants WHERE ParentID="+applID).ToString();
					try{
						SQL.UpdateDataTable(dsTrades.t_Applicants,db,sql+where+" ORDER BY ApplicantID");
					}catch(SqlException exc){
						throw new Exception(HttpUtility.HtmlEncode(SQL.XmlInnerText(dsTrades.t_Applicants)),exc);
					}
				}
				#region Address
				foreach( Trades.t_AddressRow drAddress in dsTrades.t_Address ){
					string select = "SELECT Street, CityName, Zip, StateCode, [Index] FROM  t_Address WHERE ApplicantID = "+applID+" AND [Index] = "+drAddress.Index;
					DataTable dtAddress = SQL.ExecuteDataset(db,CommandType.Text,"EXEC s_AddAddress "+applID+","+drAddress.Index+";"+select).Tables[0];
					foreach( DataColumn dcAddress in dtAddress.Columns )
						if( dtAddress.Rows[0][dcAddress.ColumnName] == System.DBNull.Value )
							SQL.UpdateField(db,select,dcAddress.ColumnName,drAddress[dcAddress.ColumnName]);
				} {
						#endregion
					
						//SqlCommandBuilder cb = new SqlCommandBuilder(new SqlDataAdapter("SELECT * FROM t_CreditInfo",conn));
						//NormalizeUpdateCommand(cb.DataAdapter.InsertCommand=cb.GetUpdateCommand(),dsTrades.t_CreditInfo.Columns).CommandText = Regex.Replace(cb.DataAdapter.InsertCommand.CommandText," WHERE .+"," WHERE ApplicantID="+applID,RegexOptions.IgnoreCase);
						//string sql = @";UPDATE t_CreditInfo SET HumanReadable = NULL FROM  t_CreditInfo c INNER JOIN t_applicants a ON c.Applicantid = a.applicantid WHERE (a.ParentID = @p1)";
						//cb.DataAdapter.InsertCommand.CommandText += sql;
						dsTrades.t_CreditInfo.Rows[0]["ApplicantID"] = applID;
						if( coApplID > 0 && dsTrades.t_CreditInfo.Rows.Count > 1 )
							dsTrades.t_CreditInfo.Rows[1]["ApplicantID"] = coApplID;

						SQL.UpdateDataTable(dsTrades.t_CreditInfo,conn,"SELECT * FROM t_CreditInfo WHERE ApplicantID=@ApplID OR (ApplicantID=@CoApplID AND ApplicantID<>0)",applID,coApplID);
					} {
							SQL.ExecuteNonQueryEx(db,"DELETE FROM t_TradeReferences WHERE ApplicantID = @ApplID",applID);
							foreach( DataRow r in dsTrades.t_TradeReferences.Rows)
								r["ApplicantID"] = applID;
							SqlDataAdapter da = new SqlDataAdapter("SELECT * FROM t_TradeReferences",conn);
							SqlCommandBuilder cb = new SqlCommandBuilder(da);
							//da.InsertCommand = cb.GetInsertCommand();
							da.UpdateCommand = cb.GetInsertCommand();
							try{
								da.Update(dsTrades.t_TradeReferences);
							}catch(SqlException){
								#region Error Check
								foreach( DataRow row in dsTrades.t_TradeReferences.Rows )
									if( row.HasErrors ){
										DataColumn[] cols = row.GetColumnsInError();
										ArrayList alErrors = new ArrayList();
										foreach( DataColumn col in dsTrades.t_TradeReferences.Columns )
											alErrors.Add(col.ColumnName+":["+row[col.ColumnName]+"]/"+row[col.ColumnName].ToString().Length+" bytes");
										string error = string.Join("\n",(string[])alErrors.ToArray(typeof(string)));
										alErrors.Clear();
										foreach( DataColumn col in cols)
											alErrors.Add(row.GetColumnError(col));
										throw new Exception(error+"\n"+string.Join("\n",(string[])alErrors.ToArray(typeof(string))));
									}
								#endregion Error Check
							}
						}
			}
			string reportError = "";
			DataRow row1 = SQL.ExecuteDatasetEx(db,"SELECT * FROM vx_ApplicantForCredit WHERE ApplicantID="+applID).Tables[0].Rows[0];
			if( !(bool)row1["Pulled"] ) reportError += row1["HumanReadable"];
			if( (int)row1["CoApplicantID"] > 0 && !(bool)row1["Pulled1"] ) reportError += "\n" + row1["HumanReadable1"];
			if( reportError != "" )throw new CreditReportException(reportError);
		}
		private SqlCommand NormalizeUpdateCommand(SqlCommand comm,DataColumnCollection columns){
			foreach( SqlParameter p in comm.Parameters )
				if( columns.Contains(p.SourceColumn) && columns[p.SourceColumn].ReadOnly )
					comm.CommandText = Regex.Replace(comm.CommandText,@",?\s*"+p.SourceColumn+@"\s*=\s*"+p.ParameterName,"",RegexOptions.IgnoreCase);
			return comm;
		}
		*/
		#endregion ======== Credit Reportd ========

		#region Database Info
		public string GetAddresses(){
			DataTable dt = SQL.ExecuteDatasetEx(db,
				"SELECT a.* FROM t_Address a INNER JOIN v_Borrower_0 b ON a.BorrowerID = b.ID WHERE "+ApplicantFilter()).Tables[0];
			while( dt.Rows.Count < 2){
				DataRow row = dt.NewRow();
				row["BorrowerID"] = dt.Rows[0]["BorrowerID"];
				row["Pos"] = dt.Rows.Count;
				dt.Rows.Add(row);
			}
			return SQL.XmlInnerText(dt.DataSet);
		}
		public string GetEmployments(int borrowerPos){
			DataTable dt = SQL.ExecuteDatasetEx(db,
				"SELECT e.* FROM t_Employment e INNER JOIN v_Borrower_"+borrowerPos+" b ON b.ID = e.BorrowerID WHERE "+ApplicantFilter()).Tables[0];
			while( dt.Rows.Count == 1 ){
				DataRow row = dt.NewRow();
				row["BorrowerID"] = dt.Rows[0]["BorrowerID"];
				row["Pos"] = dt.Rows.Count;
				dt.Rows.Add(row);
			}
			return SQL.XmlInnerText(dt.DataSet);
		}
		#endregion

	}
}
